-- 项目客户发货量表
CREATE EXTERNAL TABLE IF NOT EXISTS jms_dm.dm_project_customer_delivery_dt(
input_date                      String           COMMENT '查询日期(运单的录入日期)',
project_customer_id             String           COMMENT '项目客户id',
project_customer_name           String           COMMENT '项目客户名称',
subordinate_agent_code          String           COMMENT '代理区编码',
subordinate_agent_name          String           COMMENT '代理区名称',
franchisee_code                 String           COMMENT '加盟商编码',
franchisee_name                 String           COMMENT '加盟商名称',
network_code                    String           COMMENT '网点编码',
network_name                    String           COMMENT '网点名称',
customer_input                  bigint           COMMENT '发货量',
company_input                   bigint           COMMENT '公司发货量'
)
COMMENT '项目客户发货量表'
PARTITIONED BY (dt string COMMENT '日期(yyyy-MM-dd)')
STORED AS PARQUET
LOCATION '/dw/hive/jms_dm.db/external/dm_project_customer_delivery_dt'
TBLPROPERTIES (
'discover.partitions'='false',
'parquet.column.index.access'='true'
);


CREATE TABLE `dwd_project_customer_delivery` (
  `input_date` date NOT NULL COMMENT "查询日期(运单的录入日期)",
  `project_customer_id` varchar(50) NULL COMMENT "项目客户id",
  `network_code` varchar(50) NULL COMMENT "网点编码",
  `project_customer_name` varchar(50) NULL COMMENT "项目客户名称",
  `subordinate_agent_code` varchar(50) NULL COMMENT "代理区编码",
  `subordinate_agent_name` varchar(50) NULL COMMENT "代理区名称",
  `franchisee_code` varchar(50) NULL COMMENT "加盟商编码",
  `franchisee_name` varchar(50) NULL COMMENT "加盟商名称",
  `network_name` varchar(50) NULL COMMENT "网点名称",
  `customer_input` bigint NULL COMMENT "发货量",
  `company_input` bigint NULL COMMENT "公司发货量"
) ENGINE=OLAP
DUPLICATE KEY(`input_date`, `project_customer_id`, `network_code`)
COMMENT "项目客户发货量表"
PARTITION BY RANGE(`input_date`)
(PARTITION p20211001 VALUES [('2021-10-01'), ('2021-10-02')),
PARTITION p20211002 VALUES [('2021-10-02'), ('2021-10-03')),
PARTITION p20211003 VALUES [('2021-10-03'), ('2021-10-04')),
PARTITION p20211004 VALUES [('2021-10-04'), ('2021-10-05')),
PARTITION p20211005 VALUES [('2021-10-05'), ('2021-10-06')),
PARTITION p20211006 VALUES [('2021-10-06'), ('2021-10-07')),
PARTITION p20211007 VALUES [('2021-10-07'), ('2021-10-08')),
PARTITION p20211008 VALUES [('2021-10-08'), ('2021-10-09')),
PARTITION p20211009 VALUES [('2021-10-09'), ('2021-10-10')),
PARTITION p20211010 VALUES [('2021-10-10'), ('2021-10-11')),
PARTITION p20211011 VALUES [('2021-10-11'), ('2021-10-12')),
PARTITION p20211012 VALUES [('2021-10-12'), ('2021-10-13')),
PARTITION p20211013 VALUES [('2021-10-13'), ('2021-10-14')),
PARTITION p20211014 VALUES [('2021-10-14'), ('2021-10-15')),
PARTITION p20211015 VALUES [('2021-10-15'), ('2021-10-16')),
PARTITION p20211016 VALUES [('2021-10-16'), ('2021-10-17')),
PARTITION p20211017 VALUES [('2021-10-17'), ('2021-10-18')),
PARTITION p20211018 VALUES [('2021-10-18'), ('2021-10-19')),
PARTITION p20211019 VALUES [('2021-10-19'), ('2021-10-20')),
PARTITION p20211020 VALUES [('2021-10-20'), ('2021-10-21')),
PARTITION p20211021 VALUES [('2021-10-21'), ('2021-10-22')),
PARTITION p20211022 VALUES [('2021-10-22'), ('2021-10-23')),
PARTITION p20211023 VALUES [('2021-10-23'), ('2021-10-24')),
PARTITION p20211024 VALUES [('2021-10-24'), ('2021-10-25')),
PARTITION p20211025 VALUES [('2021-10-25'), ('2021-10-26')),
PARTITION p20211026 VALUES [('2021-10-26'), ('2021-10-27')),
PARTITION p20211027 VALUES [('2021-10-27'), ('2021-10-28')),
PARTITION p20211028 VALUES [('2021-10-28'), ('2021-10-29')),
PARTITION p20211029 VALUES [('2021-10-29'), ('2021-10-30')),
PARTITION p20211030 VALUES [('2021-10-30'), ('2021-10-31')),
PARTITION p20211031 VALUES [('2021-10-31'), ('2021-11-01')),
PARTITION p20211101 VALUES [('2021-11-01'), ('2021-11-02')),
PARTITION p20211102 VALUES [('2021-11-02'), ('2021-11-03')),
PARTITION p20211103 VALUES [('2021-11-03'), ('2021-11-04')),
PARTITION p20211104 VALUES [('2021-11-04'), ('2021-11-05')),
PARTITION p20211105 VALUES [('2021-11-05'), ('2021-11-06')),
PARTITION p20211106 VALUES [('2021-11-06'), ('2021-11-07')),
PARTITION p20211107 VALUES [('2021-11-07'), ('2021-11-08')),
PARTITION p20211108 VALUES [('2021-11-08'), ('2021-11-09')),
PARTITION p20211109 VALUES [('2021-11-09'), ('2021-11-10')),
PARTITION p20211110 VALUES [('2021-11-10'), ('2021-11-11')),
PARTITION p20211111 VALUES [('2021-11-11'), ('2021-11-12')),
PARTITION p20211112 VALUES [('2021-11-12'), ('2021-11-13')),
PARTITION p20211113 VALUES [('2021-11-13'), ('2021-11-14')),
PARTITION p20211114 VALUES [('2021-11-14'), ('2021-11-15')),
PARTITION p20211115 VALUES [('2021-11-15'), ('2021-11-16')),
PARTITION p20211116 VALUES [('2021-11-16'), ('2021-11-17')),
PARTITION p20211117 VALUES [('2021-11-17'), ('2021-11-18')),
PARTITION p20211118 VALUES [('2021-11-18'), ('2021-11-19')),
PARTITION p20211119 VALUES [('2021-11-19'), ('2021-11-20')),
PARTITION p20211120 VALUES [('2021-11-20'), ('2021-11-21')),
PARTITION p20211121 VALUES [('2021-11-21'), ('2021-11-22')),
PARTITION p20211122 VALUES [('2021-11-22'), ('2021-11-23')),
PARTITION p20211123 VALUES [('2021-11-23'), ('2021-11-24')),
PARTITION p20211124 VALUES [('2021-11-24'), ('2021-11-25')),
PARTITION p20211125 VALUES [('2021-11-25'), ('2021-11-26')),
PARTITION p20211126 VALUES [('2021-11-26'), ('2021-11-27')),
PARTITION p20211127 VALUES [('2021-11-27'), ('2021-11-28')),
PARTITION p20211128 VALUES [('2021-11-28'), ('2021-11-29')),
PARTITION p20211129 VALUES [('2021-11-29'), ('2021-11-30')),
PARTITION p20211130 VALUES [('2021-11-30'), ('2021-12-01')),
PARTITION p20211201 VALUES [('2021-12-01'), ('2021-12-02')),
PARTITION p20211202 VALUES [('2021-12-02'), ('2021-12-03')),
PARTITION p20211203 VALUES [('2021-12-03'), ('2021-12-04')),
PARTITION p20211204 VALUES [('2021-12-04'), ('2021-12-05')),
PARTITION p20211205 VALUES [('2021-12-05'), ('2021-12-06')),
PARTITION p20211206 VALUES [('2021-12-06'), ('2021-12-07')),
PARTITION p20211207 VALUES [('2021-12-07'), ('2021-12-08')),
PARTITION p20211208 VALUES [('2021-12-08'), ('2021-12-09')),
PARTITION p20211209 VALUES [('2021-12-09'), ('2021-12-10')),
PARTITION p20211210 VALUES [('2021-12-10'), ('2021-12-11')),
PARTITION p20211211 VALUES [('2021-12-11'), ('2021-12-12')),
PARTITION p20211212 VALUES [('2021-12-12'), ('2021-12-13')),
PARTITION p20211213 VALUES [('2021-12-13'), ('2021-12-14')),
PARTITION p20211214 VALUES [('2021-12-14'), ('2021-12-15')),
PARTITION p20211215 VALUES [('2021-12-15'), ('2021-12-16')),
PARTITION p20211216 VALUES [('2021-12-16'), ('2021-12-17')),
PARTITION p20211217 VALUES [('2021-12-17'), ('2021-12-18')),
PARTITION p20211218 VALUES [('2021-12-18'), ('2021-12-19')),
PARTITION p20211219 VALUES [('2021-12-19'), ('2021-12-20')),
PARTITION p20211220 VALUES [('2021-12-20'), ('2021-12-21')),
PARTITION p20211221 VALUES [('2021-12-21'), ('2021-12-22')),
PARTITION p20211222 VALUES [('2021-12-22'), ('2021-12-23')),
PARTITION p20211223 VALUES [('2021-12-23'), ('2021-12-24')),
PARTITION p20211224 VALUES [('2021-12-24'), ('2021-12-25')),
PARTITION p20211225 VALUES [('2021-12-25'), ('2021-12-26')),
PARTITION p20211226 VALUES [('2021-12-26'), ('2021-12-27')))
DISTRIBUTED BY HASH(`project_customer_id`) BUCKETS 3
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-100",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "3",
"in_memory" = "false",
"storage_format" = "V2"
);